<?php

/**
 * AppParentsModel short summary.
 *
 * AppParentsModel description.
 *
 * @version 1.0
 * @author Administrator
 */
Namespace Api\Model;
use Think\Model;
class AppLessonModel extends Model
{
    public function getMyMaterialDb(){
        return MwithNoPrefix("app_MyMaterial");
    }
    /**
     * 根据请求ID找到数据库对应的ID
     * @param mixed $iInputType 
     */
    public function getMoErDuoTypeIDbyRequestID($iInputType){
       /*
         * 请求ID: //1:听儿歌 2：听诗歌 3：听对话 4：听绘本 5:阅读 6:口语
         * 
        * 数据库ID: 1	听儿歌
        3	听诗歌
        4	听对话
        5	听绘本
        * */
        switch($iInputType)
       {
          case 1:
              return 1;
              break;
          case 2:
              return 3;
              break;
          case 3:
              return 4;
              break;
          case 4:
              return 5;
              break;
          default:
              return -1;
              break;
       }
    }
    public function getMoerduoCollectionTypeID(){
       return 1;
    }
    
    public function getYueduCollectionTypeID(){
        return 2;
    }
    
    public function getKouyueCollectionTypeID(){
        return 3;
    }
    /**
     * 获取儿歌的分类ID
     * @return mixed
     */
    public function getErGeTypeID(){
        return "1";
    }
    /**
     * 获取诗歌的分类ID
     */
    public function getShiGeTypeID(){
        return "3";
    }
    
    public function getChapterContentDb(){
        return MwithNoPrefix("BK_ChapterContent");
    }
    
    public function getCourseChapterDb(){
         return MwithNoPrefix("C_CourseChapter");
    }
    
    public function getCourseCategoryDb(){
        return MwithNoPrefix("C_CourseCategory");
    }
    
    public function getClassworksDb(){
         return MwithNoPrefix("APP_Classworks");
    }
    public function getClassworksTypeDb(){
        return MwithNoPrefix("APP_ClassworksType");
    }
    
    public function getClassworksDetailDb(){
        return MwithNoPrefix("APP_ClassworksDetail");
    }
    
    public function getYueDuTypeDb(){
        return MwithNoPrefix("APP_YueDuType");
    }
  
    public function getWxReadBookDb(){
        return MwithNoPrefix("tb_wxreadbook");
    }
    public function getClassStudentDb(){
        return  MwithNoPrefix("tb_classstudent");
    }
          
    public function getScheduleDb(){
        return  MwithNoPrefix("app_schedule");
    }
    public function getScheduleCacheDb(){
        return  MwithNoPrefix("app_schedulecache");
    }
    /**
     * 获取推荐DB
     * @return mixed
     */
    public function getRecommendListDb(){
        return MwithNoPrefix("app_recommendlist");
    }
    /**
     *   打卡数据库
     * */
    public function getCardDb(){
        return MwithNoPrefix("app_cardlog");
    }
    
    /****获取口语分类数据库
     */
    public function getKouYuTypeDb(){
        return MwithNoPrefix("APP_KouYuType");
    }
    /**
     * 磨耳朵分类数据库
     * @return mixed
     */
    public function getMoErDuoTypeDb(){
        return MwithNoPrefix("app_moerduotype");
    }
    public function getOfflineLessonList($strStartDbDate,$strEndDbDate,$strUsername)
    {
        // 加 DISTINCT 因为原有课程存在重复排课的情况，要过滤
        $strFormatSql="select DISTINCT TB_Class.classname,TB_ClassStudent.studentid , TB_CourseTable.content,TB_Class.classCode,regtime,TB_CourseTable.ID as  CourseTableID from TB_CourseTable inner join TB_Class on TB_CourseTable.classCode=TB_Class.classCode inner join TB_ClassStudent on TB_Class.classCode=TB_ClassStudent.classCode where studentid='%s' and 
datediff(d,regtime,'%s')<=0 and datediff(d,regtime,'%s')>=0";
        $db =  $this->getClassStudentDb();
        $arrResult=$db->query($strFormatSql,$strUsername,$strStartDbDate,$strEndDbDate);
        // die($db->getLastSql());
        return $arrResult;
    }
    
    /**
     * 检查计划是否存在
     * @param mixed $iScheduleID 
     */
    public function isScheduleRecordExists($iScheduleID)
    {
       $db =$this->getScheduleDb();
       $arrSchedule =  $db->where("id=%d",$iScheduleID)->field("id")->find();
       if($arrSchedule==null)
       {
           return false;
       }else{
           return true;
       }
       
    }
    /**
     * 更新计划缓存,每次修改计划都生成计划缓存
     * @param mixed $strUsername 
     * @param mixed $iScheduleId 
     * @param mixed $strDbDate 
     * @param mixed $strStart 
     * @param mixed $strStop 
     * @param mixed $iTotalDays 
     * @param mixed $imaterialId 
     */
    public function updateScheduleCache($strUsername,$iScheduleId,$strDbDate,$strStart,$strStop,$iTotalDays,$imaterialId){
        if($iTotalDays<0)$iTotalDays=1; // $iTotalDays 循环的天数 包含开始的那一天
        if(!$this->isScheduleRecordExists($iScheduleId))
        {
            return;
        }
        $dbSchedule = $this->getScheduleDb();
        $iID = $iScheduleId;
        $dbCache = $this->getScheduleCacheDb();
        $dbCache->where("scheduleid=%d and username='%s'",$iID,$strUsername)->delete();
        //die($dbCache->getLastSql());
            for($i=0;$i<$iTotalDays;$i++)
            {
                $strCalcDate =date("Y-m-d",strtotime($strDbDate." + ".$i." days "));
                $strStartTime = $strCalcDate." ".$strStart;
                $strStopTime  = $strCalcDate." ".$strStop;
                $dbCache->add(array(
                    "startDate"=>$row,
                    "starttime"=>$strStartTime,
                    "endtime"=>$strStopTime,
                    "scheduleId"=>$iID,
                    "username"=>$strUsername,
                    "materialId"=>$imaterialId
                    ));
            }
    }
    function getAllScheduleDates($strInputDate,$arrInWeek,$iTotalDays){
        $arrScheduleDates = array();
        $bIsRun = true;
        $arrDates =getAWeekTimeSlot($strInputDate,1);//返回本星期对应的列表
        $iInputTime = strtotime($strInputDate);
        $strMondayDate =  $arrDates[0];
        while($bIsRun)
        {
            foreach($arrInWeek as $row){
                
                $iAddDays = $row-1; // 1-7 对应0-6添加日期
                $strCalcDate =date("Y-m-d",strtotime($strMondayDate." + ".$iAddDays." days "));
                if(strtotime($strCalcDate)>=$iInputTime)
                {
                    $arrScheduleDates[]=$strCalcDate;
                    if(count($arrScheduleDates)>=$iTotalDays)return $arrScheduleDates;
                }
            }
            $strMondayDate =date("Y-m-d", strtotime($strMondayDate." + 7 days "));
        } //  while($bIsRun)
        return $arrScheduleDates;
    } //  public function getAllScheduleDates(){
    
    /**
     * 获取线上课程
     * c.startDate,c.starttime,c.endtime,b.BookName,c.scheduleId,c.materialId
     * @param mixed $strStartDate 
     * @param mixed $strEndDate 
     * @param mixed $strUsername 
     * @return mixed
     */
    public function getOnlineLesson($strStartDate,$strEndDate,$strUsername){
        $strSql = "select c.startDate,c.starttime,c.endtime,b.BookName,c.scheduleId,c.materialId
 from APP_ScheduleCache c left join TB_WxReadBook b on b.ID = c.materialId
 where username='%s' and datediff(d,c.endtime,'%s')<=0 and datediff(d,c.endtime,'%s')>=0 order by c.id desc";
        $db = $this->getScheduleCacheDb();
        $arrResult1 = $db ->query($strSql,$strUsername,$strStartDate,$strEndDate);
        //die($db->getLastSql());
        return $arrResult1;
    }
    
    public function getTodayCard($strUsername){
        /*
         * “” : “60”   //分钟-磨耳朵时长
    “” : “60”    //阅读时长
    “” : “60” //口语时长
    “” : “50”  //自然拼读时长
    “”: “40” //背单词时长

         * */
        $db = $this->getCardDb();
        // 语句错误仅测试用
        $arr =  $db->where("username='%s' and datediff(d,addtime,getdate())=0 ",$strUsername)->field("max(cardtype) as cardtype,sum(cardminutes) as sumcardminutes")->group("cardtype")->select();
        //die($db->getLastSql());
        return $arr;
        //  return $db->where("username='%s' and datediff(d,addtime,getdate())=0 ",$strUsername)->field("max(cardtype) as cardtype,sum(cardminutes) as sumcardminutes")->group("cardtype");
    }
    /**
     * 获取历史记录
     * @param mixed $strUsername 
     * @return mixed
     */
    public function getHistoryCard($strUsername){
        /*
         * “” : “60”   //分钟-磨耳朵时长
        “” : “60”    //阅读时长
        “” : “60” //口语时长
        “” : “50”  //自然拼读时长
        “”: “40” //背单词时长

         * */
        $db = $this->getCardDb();
        return $db->where("username='%s'",$strUsername)->field("max(cardtype) as cardtype,sum(cardminutes) as sumcardminutes")->group("cardtype");
    }
    /**
     * 获取推荐内容
     */
    public function getRecommendList($strPos){
      $db = $this->getRecommendListDb();
      $arrResult0 = $db->where("pos = '%s' and status=1",$strPos)->order("ord asc")->select();
      $arrResult=array();
      foreach($arrResult0 as $row){
          $arrResult[]=array("name"=>$row["name"]
              ,"imageUrl"=>$row["imageurl"],
              "type"=>$row["type"],
              "content"=>$row["recommendcontent"]); 
      }
      return $arrResult; 
    }
    
    public function getRecommendListWithReadTimes($strPos){
        $db = $this->getRecommendListDb();
        $arrResult0 = $db->query("select r.*,b.ReadTimes,b.AppDuration from APP_RecommendList r left join TB_WxReadBook b on r.RecommendContent=b.ID where r.pos='%s' and r.status=1 order by r.ord asc",$strPos);
        $arrResult=array();
        foreach($arrResult0 as $row){
           
            switch($row["type"])
            {
                case 3: // 歌曲
                    $icount = $row["readtimes"];
                    $iduration =$row["appduration"];
                    break;
                default: 
                    $icount = 0;
                    $iduration = 0;
                    break;
            }
            $arrResult[]=array("name"=>$row["name"]
                ,"imageUrl"=>$row["imageurl"],
                "type"=>$row["type"],
                "content"=>$row["content"],
                "count"=>$icount,
                "duration"=>$iduration);
        }
        return $arrResult; 
    }
    
    /**
     * 磨100h5链接
     * @return mixed
     */
    public function getlistening100Url(){
        return "http://h5.taobao.com/";
    }
    
    /**
     * 动画100链接
     * @return mixed
     */
    public function getanimation100Url(){
        return "http://www.baidu.com/";
    }
 
    public function getKouyuByTypeID($iType)
    {
        $db = $this->getKouYuTypeDb();
        $arrResult0 = $db->order("ord asc")->where("CategoryParentID=%d",$iType)->field("ID,CategoryName,Ord,CategoryParentID")->select();
        $arrResult=array();
        foreach($arrResult0 as $row)
        {
            $iRowType = $row["id"];
            $arrResult[]=array(
                "title"=>$row["categoryname"],
                "classId"=>$iRowType,
                "subClassList" =>$this->getKouyuByTypeID($iRowType)

                );
        }
        return $arrResult;
    }
    
    public function getYueDuByTypeID($iType)
    {
        $db = $this->getYueDuTypeDb();
        $arrResult0 = $db->order("ord asc")->where("CategoryParentID=%d",$iType)->field("ID,CategoryName,Ord,CategoryParentID")->select();
        $arrResult=array();
        foreach($arrResult0 as $row)
        {
            $iRowType = $row["id"];
            $arrResult[]=array(
                "title"=>$row["categoryname"],
                "classId"=>$iRowType,
                "subClassList" =>$this->getYueDuByTypeID($iRowType)

                );
        }
        return $arrResult;
    }
    
    public function getMoErDuoByTypeID($iType)
    {
        $db = $this->getMoErDuoTypeDb();
        $arrResult0 = $db->order("ord asc")->where("CategoryParentID=%d",$iType)->field("ID,CategoryName,Ord,CategoryParentID")->select();
        $arrResult=array();
        foreach($arrResult0 as $row)
        {
            $iRowType = $row["id"];
            $arrResult[]=array(
                "title"=>$row["categoryname"],
                "classId"=>$iRowType,
                "subClassList" =>$this->getMoErDuoByTypeID($iRowType)

                );
        }
        return $arrResult;
    }
    //public function getMoErDuoTypeList(){
    //    $db = $this->getMoErDuoTypeDb();
    //    $arrResult0 = $db->order("ord asc")->select();
    //    $arrResult = array();
    //    foreach($arrResult0 as $row){
    //        $arrResult[] = array(
    //            "title"=>$row["categoryname"],
    //            "classId"=>$row["id"]
    //            );
    //    }
    //    return $arrResult;
    //}
    public function getMusicClasses($iDbTypeID){
        //$iMusicClassID = $this->getErGeTypeID() ;// 儿歌分类ID'
        $db = $this->getMoErDuoTypeDb();
        $arrResult0 = $db->where("CategoryParentID=%d",$iDbTypeID)->order("ord asc")->select();
        $arrResult = array();
        foreach($arrResult0 as $row){
          $arrResult[] = array(
              "title"=>$row["categoryname"],
              "classId"=>$row["id"]
              );
        }
        return $arrResult;
    }
    public function getMoErDuoBooksByTypeID($iCategoryID){
         $dbBook = $this->getWxReadBookDb();
         $strFormatSql = "SELECT b.[bookname],b.[id],b.[DirName] FROM [tb_wxreadbook] b  WHERE ( ','+AppMoErDuoTypeIDList+',' like ',%d,' ) ";
         $arrResult0 = $dbBook->query($strFormatSql,$iCategoryID);
         $arrResult=array();
         foreach($arrResult0 as $row)
         {
            /*“materialId”: 11,//教材id
            “name”: “”,//教材，名称
            “imageUrl”: “”//教材封面
             */
             $arrResult[] = array(
                 "materialId"=>$row["id"],
                 "name"=>$row["bookname"],
                 "imageUrl"=>$this->getBookCoverUrl($row["dirname"])
                 );
         }
         return $arrResult;
    }
    
    public function getYueduBooksByTypeID($iCategoryID){
        $dbBook = $this->getWxReadBookDb();
        $strFormatSql = "SELECT b.[bookname],b.[id],b.[DirName] FROM [tb_wxreadbook] b  WHERE ( ','+AppYueDuTypeIDList+',' like ',%d,' ) ";
        $arrResult0 = $dbBook->query($strFormatSql,$iCategoryID);
        $arrResult=array();
        foreach($arrResult0 as $row)
        {
            /*“materialId”: 11,//教材id
            “name”: “”,//教材，名称
            “imageUrl”: “”//教材封面
             */
            $arrResult[] = array(
                "materialId"=>$row["id"],
                "name"=>$row["bookname"],
                "imageUrl"=>$this->getBookCoverUrl($row["dirname"])
                );
        }
        return $arrResult;
    }
    
    public function getKouyuBooksByTypeID($iCategoryID){
        $dbBook = $this->getWxReadBookDb();
        $strFormatSql = "SELECT b.[bookname],b.[id],b.[DirName] FROM [tb_wxreadbook] b  WHERE ( ','+AppKouYuTypeIDList+',' like ',%d,' ) ";
        $arrResult0 = $dbBook->query($strFormatSql,$iCategoryID);
        $arrResult=array();
        foreach($arrResult0 as $row)
        {
            /*“materialId”: 11,//教材id
            “name”: “”,//教材，名称
            “imageUrl”: “”//教材封面
             */
            $arrResult[] = array(
                "materialId"=>$row["id"],
                "name"=>$row["bookname"],
                "imageUrl"=>$this->getBookCoverUrl($row["dirname"])
                );
        }
        return $arrResult;
    }
    public function getBookList($iCategoryID,$strUsername){
        /*
         *   “bookName” : “”,  //书籍名称
        “bookImageUrl” : “” //书籍封面链接
        “count” : 128 //阅读次数
        “bookId” : 124

         * */
      $db = $this->getWxReadBookDb();
      $strFormatSql = "SELECT b.[bookname],b.[ReadTimes],b.[id],b.[DirName],b.[Ord],ISNULL(c.id, 0) as collectionid,ISNULL(m.id,0) as MyMaterialID FROM [tb_wxreadbook] b left join (select * from APP_Collections where APP_Collections.username='%s' and APP_Collections.type=".$this->getMoerduoCollectionTypeID().") c
on b.ID=c.courseId left join (select id ,wxbookid from app_MyMaterial where username='%s' ) m on b.ID=m.wxbookid WHERE ( ','+AppMoErDuoTypeIDList+',' like ',%d,' ) ";
      
      $arrResult0 = $db->query($strFormatSql,$strUsername,$strUsername,$iCategoryID);
      //$arrResult0 = $db->where("','+AppMoErDuoTypeIDList+',' like ',%d,'",$iCategoryID)->field("bookname,ReadTimes,id,DirName,Ord")->order("Ord asc")->select();
      $arrResult=array();
      foreach($arrResult0 as $row){
          $strbookImageUrl =$this->getBookCoverUrl($row["dirname"]);
          $iIsJoinMaterial =$row["mymaterialid"]>0?1:0;
          $icollectionid =$row["collectionid"];
          $iIsCollect = $icollectionid>0?1:0;
         $arrResult[]=array(
             "bookName"=>$row["bookname"],
             "bookImageUrl"=>$strbookImageUrl,
             "count"=>$row["readtimes"],
             "bookId"=>$row["id"],
             "isCollected"=>$iIsCollect,
             "isJoinMaterial"=>$iIsJoinMaterial
             );
      }
      return $arrResult;
    }
    
    public function getPoetryList($strUsername){
        // $strUsername =$this->getDefaultUserName();
        $iCate = $this->getShiGeTypeID() ;  // iPoetryID 诗歌列表
        $arrResult = $this->getBookList($iCate,$strUsername);
        return $strPoetryId;
    }
    
    /**
     * 取阅读的子分类
     * CategoryName,ID
     */
    public function getYueDuSubType(){
        $db=$this->getYueDuTypeDb();
        return  $db->field("CategoryName,ID")->select();
    }
    

    
        /**
     * 取阅读的子分类
     * CategoryName,ID
     */
    public function getKouYuSubType(){
        $db=$this->getKouYuTypeDb();
        return  $db->field("CategoryName,ID")->select();
    }
       
    public function getBookCoverUrl($strDirName)
    {
        $strbookImageUrl ="http://osscdn.anniekids.net/weidiandu/".$strDirName."/pic/fm.jpg";
        return $strbookImageUrl;
    }
    
    /**
     * 根据阅读分类，查找对应的书
     * collectionid,b.bookname,b.ReadTimes,b.id,b.dirname,b.ord
     * @param mixed $strCollectionUsername 
     * @param mixed $iYueDuTypeID 
     * @return mixed
     */
    public function getBookListByYueDuType($strCollectionUsername,$iYueDuTypeID){
        $strSql="select isnull(c.id,0) as collectionid,b.bookname,b.ReadTimes,b.id,b.dirname,b.ord from TB_WxReadBook b left join (select * from APP_Collections where APP_Collections.username='%s' and APP_Collections.type=".$this->getYueduCollectionTypeID().") c
on b.ID=c.courseId where ','+b.AppYueDuTypeIDList+',' like ',%d,' order by b.ord asc "; // APP_Collections.type=2 只检查阅读这块
        $db = $this->getWxReadBookDb();
        $arrResult0 = $db->query($strSql,$strCollectionUsername,$iYueDuTypeID);
        return $arrResult0;    
    }
    
    /**
     * 获取线下课程
     * classCode	classname	CourseParentType	timenumber	SurplusNumber
     *  83502	AS1-Phase 1	年度课	120	112
     * @param mixed $strUsername 
     * @return mixed
     */
    public function getCoursesOffline($strUsername){
       $strSql="select TB_Class.classCode,(case when TB_Class.CourseParentType=1 then TB_Class.KeCheng+'-'+TB_Class.NianDu else (select TB_CourseType.CourseName from TB_CourseType where TB_CourseType.ID=TB_Class.coursetype) end) as classname, 
(case when TB_Class.CourseParentType=1 then '年度课'  when TB_Class.CourseParentType=2 then '活动课' else  '特训营' end) as CourseParentType,
(TB_Class.classnum*TB_Class.UnitClassTime) as timenumber,
((SELECT IsNull(Sum(classhours),0) as TotalClasshours From TB_StudentRenew WHERE status=1 And is_delete=0  And studentid=TB_ClassStudent.studentid And classcode=TB_Class.classCode)-
(SELECT IsNull(Sum(t.UnitClassTime),0) as SumClassHours FROM [TB_AttendRecord] a Inner Join [TB_CourseTable] t ON a.CourseTableID=t.id where (a.status=1 or a.status=2) And a.is_delete=0 And t.classCode=
TB_Class.classCode And a.studentid=(SELECT [id] FROM [TB_Students] Where [username]=
TB_ClassStudent.studentid))-(SELECT IsNull(Sum(hour),0) as SumClassHours FROM [TB_StudentClassHourAdjust] where studentid=TB_ClassStudent.studentid and originalclasscode=TB_Class.classCode)) as SurplusNumber
from TB_ClassStudent
inner join TB_Class on TB_ClassStudent.classCode=TB_Class.classCode
where TB_ClassStudent.studentid='%s'";
      $arrResult = $this->getClassStudentDb()->query($strSql,$strUsername);
      return $arrResult;
    }
    /**
     * 取课程对应的图标
     * @param mixed $arrTypeList 
     * @param mixed $strClassName 
     * @return mixed
     */
    public function getClassIconID($arrTypeList,$strClassName){
        $i=0;
        $arrTemp = explode("-",$strClassName);
        $strTemp = trim(strtoupper($arrTemp[0]));
        foreach($arrTypeList as $strType)
        {
            $i++;
            if($strType==$strTemp){
                return $i;
            }
          
        }
        return 1 ; // 没找到对应分类取第一个
    }
    /**
     * 获取所有的图标和课程映射类型的列表
     */
    public function getTypeIconMapList(){
        $str="AS1,AS2,AP0,AP1,AP2,AP3,AP4,磨100,拼100,亲100,双100,项目演讲,语法口语,动画口语,流利阅读,阅读进阶,听说突破,自然拼专项";
        return explode(",",$str);
    }
    
    /**
     * 从计划中获取在线课程列表
     * [FinishCnt], [TotalCnt], [materialId], [BookName], [DirName]
     * @param mixed $strUsername 
     */
    public function getCourseOnline($strUsername){
        $strSql = "select temp.*,b.BookName,b.DirName from ( 
	 select sum(IsFinish) as FinishCnt,count(*) as TotalCnt,max(materialId) as materialId from (
	select case  when endtime<getdate() then 1 else 0 end as IsFinish,scheduleId,materialId  from APP_ScheduleCache where username='%s'
	) a group by scheduleId
) temp LEFT JOIN TB_WxReadBook b on temp.materialId = b.ID";
        return $this->getScheduleCacheDb()->query($strSql,$strUsername);
    }
    
    /**
     * 获取作业信息
     * ID,CourseTableID,CourseChapterID,CourseCategoryID
     * @param mixed $iCourseTableID 
     * @param mixed $iStatus 0未发布 1发布
     */
    public function getClassworkInfo($iCourseTableID,$iStatus)
    {
       $db=   $this->getClassworksDb();
       $arrInfo =   $db->where("CourseTableID=%d",$iCourseTableID)->field("ID,CourseTableID,CourseChapterID,CourseCategoryID")->find();
       return $arrInfo;
    }
    
    
    public function getClassworkTitle($iCourseChapterID){
        // 作业的CourseChapter 只有一级
        $db = $this->getCourseChapterDb();
        $arrInfo =  $db->where("Fid=%d",$iCourseChapterID)->field("FCourseCategoryID, FChapterName,Fid")->find();
        if($arrInfo==null)
        {
            return "";
        }
        $strChapterTitle = $arrInfo["fchaptername"];
        $iCatID = $arrInfo["fcoursecategoryid"];
        $dbCat = $this->getCourseCategoryDb();
        $bFind=true;
        $strCatTitle="";
        $iLevel = 0;
        while($bFind){
            $arrCatInfo = $dbCat->where("Fid=%d",$iCatID)->field("FCategoryName, FContent, FParentID")->find();
            if($arrCatInfo!=null)
            {
                $strCatTitle.=$arrCatInfo["fcategoryname"].",";
                $iCatID = $arrCatInfo["fparentid"];
            } else{
                $bFind=false;
            } 
            $iLevel++;
            if($iLevel>10)  // 限定为十层，避免进入死循环
            {
                $bFind=false;
            }
        }
        //die($strChapterTitle);
        return $strCatTitle.$strChapterTitle;
    }
    
    public function getScheduleDetail($strUsername,$iScheduleID){
       $strFormatSql = "select top 1 s.[materialId], s.[startDate],s.[totalDays], s.[start], s.[stop], b.[DirName], b.[BookName] from APP_Schedule s left join TB_WxReadBook b on s.materialId = b.ID where s.username='%s' and s.ID=%d";
      $db = $this->getScheduleDb();
      $arrResult = $db->query($strFormatSql,$strUsername,$iScheduleID);
      //die($db->getLastSql());
      if(count($arrResult)>0) return $arrResult[0];
      return null;
    }
    
    /**
     * 获取作业类型
     * [ID], [TypeCode], [TypeRequest], [TypeStatus],[ClassworksID],[ClassName]
     * @param mixed $iClassworkID 
     */
    public function getClassworksType($iClassworkID){
        $db = $this->getClassworksTypeDb();
        $arrResult =  $db->where("CourseTableID=%d",$iClassworkID)->select();
        return $arrResult;
    }
    
 
    /**
     * Summary of getClassworkDetailByTypeID
     * [Fid],[FTitle], [FContent], [FSort], [FResourceID], [FImg]
     * @param mixed $iClassworksTypeID 
     * @param mixed $strUsername 
     */
    public function getClassworkDetailByTypeID($iClassworksTypeID,$strUsername){
        $strWhereFormat = "Fid in (select BkChapterContentID from APP_ClassworksDetail d where d.ClassworksTypeID=%d AND(ForSomeOne=0 or(ForSomeOne=1 and ','+SomeOneList+',' like ',%s,' )))";
        $db = $this->getChapterContentDb();
        $arrResult = $db->where($strWhereFormat,$iClassworksTypeID,$strUsername)->field("[Fid],[FTitle], [FContent], [FSort], [FResourceID], [FImg]")->select();
        return $arrResult;
    }
    
    /**
     * 获取作业图片地址，防止将来图片地址有变化
     * @param mixed $strResourceImg 
     * @return mixed
     */
    public function getClassworkImgUrl($strResourceImg)
    {
        return $strResourceImg;
    }
    
    /**
     * 歌曲绘本作业详情
     * [Fimg], [FContent], [FTitle]
     * @param mixed $iResourceID 
     */
    public function getResourceDetail($iResourceID)
    {
        $db = $this->getChapterContentDb();
        $arrResult = $db->where("fid=%d",$iResourceID)->field("[Fimg], [FContent], [FTitle]")->find();
        return $arrResult;
    }
    
    /**
     * 获取课业的详细资源
     * @param mixed $strContent 
     * @return mixed
     */
    public function getClassworkResourceUrl($strContent)
    {
        // 功能不明确，喻好要求先随便给我网址
        return "http://osscdn.anniekids.net/weidiandu/Songs/mobaobao1/Head_Shoulders_Knees_And_Toes/sound/1a.mp3";
        //return $strContent;
    }
    public function getYueduAllSub($iType)
    {
        $dbYueDuType = $this->getYueDuTypeDb();
        $arrResult0 = $dbYueDuType->where("CategoryParentID = %d",$iType)->field("CategoryParentID,ID,CategoryName,ord")->order("ord asc")->select();
        $arrResult = array(); 
        foreach($arrResult0 as $row)
        {
            $iTypeSubID = $row["id"];
            $arrMaterialList  =  $this->getYueduBooksByTypeID($iTypeSubID);
            $arrResult[] = array(
                 "title"=>$row["categoryname"],
                 "classId"=>$iTypeSubID,
                 "materialList"=>$arrMaterialList,
                 "subClassList"=>$this->getYueduAllSub($iTypeSubID)
                ); 
        }
        return $arrResult;
    }
    
    
    public function getMoErDuoALLSub($iType)
    {
        $dbMoErDuoType = $this->getMoErDuoTypeDb();
        $arrResult0 = $dbMoErDuoType->where("CategoryParentID = %d",$iType)->field("CategoryParentID,ID,CategoryName,ord")->order("ord asc")->select();
        // die($dbMoErDuoType->getLastSql());
        $arrResult = array();
        foreach($arrResult0 as $row)
        {
            $iTypeSubID = $row["id"];
            $arrMaterialList  =  $this->getMoErDuoBooksByTypeID($iTypeSubID);
            $arrResult[] = array(
                 "title"=>$row["categoryname"],
                 "classId"=>$iTypeSubID,
                 "materialList"=>$arrMaterialList,
                 "subClassList"=>$this->getMoErDuoALLSub($iTypeSubID)
                ); 
        }
        return $arrResult;
    }
    
    public function getKouyuALLSub($iType)
    {
        $dbKouyu = $this->getKouYuTypeDb();
        $arrResult0 = $dbKouyu->where("CategoryParentID = %d",$iType)->field("CategoryParentID,ID,CategoryName,ord")->order("ord asc")->select();
        // die($dbMoErDuoType->getLastSql());
        $arrResult = array();
        foreach($arrResult0 as $row)
        {
            $iTypeSubID = $row["id"];
            $arrMaterialList  =  $this->getKouyuBooksByTypeID($iTypeSubID);
            $arrResult[] = array(
                 "title"=>$row["categoryname"],
                 "classId"=>$iTypeSubID,
                 "materialList"=>$arrMaterialList,
                 "subClassList"=>$this->getKouyuALLSub($iTypeSubID)
                ); 
        }
        return $arrResult;
    }
    
    
    public function joinMaterial($strUsername,$iBookID)
    {
        $db = $this->getMyMaterialDb();
        $arrBook =  $db->where("username='%s' and wxbookid=%d",$strUsername,$iBookID)->field("id")->find();
        if($arrBook!=null)
        {
           return "此书已经加入自选教材，请不要重复操作。";
        }
        $db->add(array(
            "wxbookid"=>$iBookID,
            "username"=>$strUsername
            ));
        return "";
    }
    
    public function cancelMaterial($strUsername,$iBookID)
    {
        $db = $this->getMyMaterialDb();
        $arrBook =  $db->where("username='%s' and wxbookid=%d",$strUsername,$iBookID)->field("id")->find();
        if($arrBook==null)
        {
            return "此书未加入自选教材。";
        }
        $db->where("id=%d",$arrBook["id"])->delete();
        return "";
    }
    
    /**
     * 获取自选教材
     * b.id,b.bookname,b.dirname
     * @param mixed $strUsername 
     */
    public function getOptionalMaterial($strUsername){
        $db = $this->getMyMaterialDb();
        $strFormatSql="select b.id,b.bookname,b.dirname from app_MyMaterial m inner join TB_WxReadBook b on m.wxbookid=b.ID
where m.username ='%s' order by m.id desc";
        return  $db->query($strFormatSql,$strUsername);
    }
}
